CRM 2011 Invalid Date/Time in the Filtered Lookup

Filtering the Lookup based upon any condition is a common approach. I had a scenario to filter the records based on date (Accounts that are having an end date “on or after” today’s date are to be displayed in the lookup). After downloaded the fetchXML, I found that fetchXML is considering the date format as “yyyy-MM-DD”.

CRM date field won’t consider this date format. By default, it will be in the “DD-MM-yyyy” format.  If I write fetchXML without formatting the date, it displays an error stating:

Image

This is the fetchXML that I have downloaded from Account entity:

<fetch version=“1.0” output-format=“xml-platform” mapping=“logical” distinct=“false”>

<entity name=“account”>

<attribute name=“name” />

<attribute name=“primarycontactid” />

<attribute name=“emailaddress1” />

<attribute name=“address1_addresstypecode” />

<attribute name=“new_date” />

<attribute name=“accountid” />

<order attribute=“name” descending=“false” />

<filter type=“and”>

<condition attribute=“address1_addresstypecode” operator=“eq” value=”1” />

      <condition attribute=“new_date” operator=“on-or-after” value= “2013-08-29” />

</filter>

</entity>

</fetch>

Henceforth, I should format the date as per the date in fetchXML. (i.e.), I need to consider the format as “yyyy-MM-DD” instead “DD-MM-yyyy”. So, I just included this code in the Script:

var today = new Date();

var futureDate = new Date(today.setDate(today.getDate()));

var dtFrmtd = futureDate.format(“yyyy-MM-dd”);

 

Now, the complete code is:

function filterLookup()

{

var addr = Xrm.Page.getAttribute(“address1_addresstypecode”).getValue();

var today = new Date();

var futureDate = new Date(today.setDate(today.getDate()));

var dtFrmtd = futureDate.format(“yyyy-MM-dd”);

var viewId = “{1DFB2B35-B07C-44D1-868D-258DEEAB88E2}”;

var entityName = “account”;

var viewDisplayName = “Accounts with the same Address type”;

var fetchXml = “<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>”+

“<entity name=’account’>”+

“<attribute name=’name’ />”+

“<attribute name=’primarycontactid’ />”+

“<attribute name=’emailaddress1′ />”+

“<attribute name=’address1_addresstypecode’ />”+

“<attribute name=’new_date’ />”+

“<attribute name=’accountid’ />”+

“<order attribute=’name’ descending=’false’ />”+

“<filter type=’and’>”+

“<condition attribute=’address1_addresstypecode’ operator=’eq’ value='”  + addr + “‘ />”+

“<condition attribute=’new_date’ operator=’on-or-after’ value='”  +dtFrmtd+ “‘ />”+

“</filter>”+

“</entity>”+

“</fetch>”;

var layoutXml = “<grid name=’resultset’ object=’1′ jump=’name’ select=’1′ icon=’1′ preview=’1′>” +

“<row name=’result’ id=’accountid’>” +

“<cell name=’name’ width=’300′ />” +

“<cell name=’primarycontactid’ width=’150′ />” +

“<cell name=’telephone1′ width=’100′ />” +

“</row>” +

“</grid>”;

Xrm.Page.getControl(“parentcustomerid”).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, true);

}

That’s it. Now, It worked Well.

Cheers,

Naren

Leave a comment